/**
 *  *2010-4-13 下午08:57:31  add 
 */
package com.base.myproject.server.tools;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.regex.Pattern;

import com.base.myproject.client.jdbc.Cell;
import com.base.myproject.client.jdbc.DataSet;
import com.base.myproject.client.jdbc.Row;
import com.base.myproject.server.database.PoolMan;

/**
 * 
 * @author Administrator
 *2010-4-13
 * javazgw@gmail.com
 */
public class DataSetTool {

	public DataSetTool()
	{
		
	}
	
	public DataSet getDataSet(String sql)
	{
		
		java.text.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd H:mm:ss");
		//String datestr = sdf.format(d );
		PoolMan pm = new PoolMan();
		// String sql =
		// "select bcode,bname from  bcode where bcode is not null and bname is not null";
		ResultSet rs = null;
		DataSet dataset = new DataSet();
		try {
			rs = pm.executeQuery(sql);

			int columncount = rs.getMetaData().getColumnCount();
			String[] column_names = new String[columncount];
			for (int i = 0; i < columncount; i++) {
				column_names[i] = rs.getMetaData().getColumnName(i + 1);
			}
			
			while (rs.next()) {
				Row row = new Row();
				for (int j = 0; j < columncount; j++) {
					Cell c = new Cell();
					c.setCellname(column_names[j]);
				//	System.out.println(column_names[j]+" == "+rs.getMetaData().getColumnType(j+1));
					//日期
					if(rs.getMetaData().getColumnType(j+1)==java.sql.Types.TIMESTAMP)
					{
						if(rs.getTimestamp(column_names[j])!=null)
						{
						String datestr = sdf.format(rs.getTimestamp(column_names[j]));
						c.setValue(datestr);
						}
						else
						{
							c.setValue(null);
						}
					}
					else
					{
					c.setValue(rs.getString(column_names[j]));
					}
					
					row.addCell(c);
				}
				dataset.addRow(row);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				pm.close("test");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return dataset;
	}
	
	/**
	 * 数据库表sqltable里定义了取数的sql，根据此表的名字获得
	 * sql语句，再通过此sql语句获得dataset
	 * @param sqlname
	 * @return
	 */
	public DataSet getDataSetFromSqlTable(String sqlname,HashMap<String, String> hmmacro)
	{

		String sql = "";
		PoolMan pm = new PoolMan();
		try {
			
			String findsql ="select sqlvalue  from sqltable where name='"+sqlname+"'";
			
			ResultSet rs = pm.executeQuery(findsql);
			rs.next();
			sql = rs.getString("sqlvalue");
		} catch (Exception e) {
		
			e.printStackTrace();
		}
		
		if(hmmacro!=null)
		for(String strmarc :hmmacro.keySet())
		{
			sql =sql.replaceAll(Pattern.quote("{"+strmarc+"}"), hmmacro.get(strmarc));
		}
		
		return  getDataSet( sql);
		
	}
	/**
	 * 
	 * @param leftds
	 * @param rightds
	 * @param leftstr
	 * @param rightstr
	 * @return
	 */
	public DataSet GetLeftJoinDataSet(Row leftrow,DataSet rightds,String[] leftstr,String[] rightstr)
	{
		DataSet ds = new DataSet();
		DataSetComparators dscomparators  = new DataSetComparators( leftstr,rightstr);
		for(int i = 0;i<rightds.getRowCount();i++)
		{
			if(dscomparators.compare(leftrow, rightds.getRow(i))==0)
			{
				ds.addRow( rightds.getRow(i));
			}
		}
		return ds;
	}
	
	public static void main(String[] argv)
	{
		DataSet ds1 = new DataSet();
		DataSet ds2 = new DataSet();
		Row r1 = new Row();
		r1.PutValue("bcode", "001");
		r1.PutValue("bname", "zgw");
		r1.PutValue("sex", "1");
		ds1.addRow(r1);
		Row r2 = new Row();
		r2.PutValue("bcode", "002");
		r2.PutValue("bname", "zgw");
		r2.PutValue("sex", "1");
		ds1.addRow(r2);
		Row r3 = new Row();
		r3.PutValue("bcode", "003");
		r3.PutValue("bname", "zgw");
		r3.PutValue("sex", "1");
		ds1.addRow(r3);
		
		//---
		
		Row rr1 = new Row();
		rr1.PutValue("bcode", "001");
		rr1.PutValue("bname", "zgw");
		rr1.PutValue("sex", "1");
		ds2.addRow(rr1);
		Row rr2 = new Row();
		rr2.PutValue("bcode", "001");
		rr2.PutValue("bname", "zgw1");
		rr2.PutValue("sex", "2");
		ds2.addRow(rr2);
		Row rr3 = new Row();
		rr3.PutValue("bcode", "003");
		rr3.PutValue("bname", "zgw");
		rr3.PutValue("sex", "3");
		ds2.addRow(rr3);
		
		DataSetTool dst = new DataSetTool();
		
		DataSet ds3 = dst.GetLeftJoinDataSet(ds1.getRow(2), ds2, new String[]{"bcode","bname"},new String[]{"bcode","bname"} );
		for(int i=0;i<ds3.getRowCount();i++)
		{
			System.out.println(ds3.getValue(i, "bcode"));
			System.out.println(ds3.getValue(i, "bname"));
			System.out.println(ds3.getValue(i, "sex"));
		}
	}
	
}
